package com.bidevalution.business.job;

import com.bidevalution.business.factory.StepJobJdbcFactory;
import com.bidevalution.business.service.JdbcTemplateService;
import com.bidevalution.dao.entity.*;
import com.qtp.core.util.DateUtil;
import com.qtp.core.util.IdsUtil;
import com.qtp.core.util.OConvertUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 规费税金评审
 * Created by glodon on 2019-10-12.
 */
public class TaxFeesComputerNewJob implements StepJobJdbcFactory {
    private JdbcTemplateService jdbcTemplateService;

    @Override
    public void setJdbcTemplateService(JdbcTemplateService jdbcTemplateService) {
        this.jdbcTemplateService = jdbcTemplateService;
    }

    @Override
    public Boolean execute(BeSectionStepJob beSectionStepJob, String submitCode, String expertId,String layouCode)
            throws Exception {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("sectionStepId", beSectionStepJob.getSectionStepId());
        paramMap.put("submitCode", submitCode);
        paramMap.put("sectionId", beSectionStepJob.getSectionId());
        RowMapper<BeSectionInfo> rm = BeanPropertyRowMapper.newInstance(BeSectionInfo.class);
        BeSectionInfo beSectionInfo = jdbcTemplateService.selectForObject(" select\n" +
                "    seq_id, section_stage, section_code, section_name, bid_type, budget_money, bid_open_time, \n" +
                "    tenderee, agent_org_code, agent_name, bid_open_address, submit_time, pt_section_id, \n" +
                "    section_area, eval_method, td_projectId, create_time, update_time" +
                "    from be_section_info\n" +
                "     where seq_id=:sectionId ", paramMap, rm);
        paramMap.put("tdProjectId", beSectionInfo.getTdProjectid());
        //获取规费税金不符合数计算结果
        List<Map<String, Object>> enterList = jdbcTemplateService.selectForList("SELECT b.seq_id enterpriseId,b.enterprise_name enterpriseName, IFNULL(c.bfhs,0) bfhs \n" +
                "FROM be_section_enterprise b\n" +
                "LEFT JOIN(select a.enterprise_id, count(1) as bfhs from\n" +
                "\t\t\t(select m.enterprise_id,m.biin_name,n.otco_Name otcoName,n.mano_ManageNodeID,n.ManageNodeName manageNodeName,n.otco_OtherCostID,\n" +
                "\t\t   round(n.Rate,2) rate,n.Unit unit,'不符合招标费率要求：'+n.UnderRate as errMemo\n" +
                "\t\t\tfrom td_bidinfo m \n" +
                "\t\t\tinner join \n" +
                "\t\t\t\t\t\t\t\t\t\t(select ProjectID,mano_ManageNodeID,otco_OtherCostID,ManageNodeName,\n" +
                "\t\t\t\t otco_Name,Type,Unit,BidID,Total,JSTotal,UnderRate,HyperRate,Rate\n" +
                "\t\t\t\t\t\t\t\t\t\t\tfrom (select a.otco_ProjectID as ProjectID,c.mano_ManageNodeID,a.otco_OtherCostID ,\n" +
                "\t\t\t\tcase when c.mano_Type=2 then d.mano_Name+' - '+c.mano_Name else c.mano_Name end as ManageNodeName,\n" +
                "\t\t\t\ta.otco_Name,a.otco_CostType as Type,'项' as Unit,a.enterprise_id as BidID  ,\n" +
                "\t\t\t\tifnull(a.otco_Total,0) as Total,\n" +
                "\t\t\t\tcase a.otco_CostType  \n" +
                "\t\t\t\twhen '4' then ifnull(e.RGFTotal,0)+ifnull(e.JXFTotal,0)+\n" +
                "\t\t\t\t\t\t\t  ifnull(f.RGFTotal,0)+ifnull(f.JXFTotal,0)\n" +
                "\t\t\t\t when '5' then ifnull(k.SJTotal,0)\n" +
                "\t\t\t\telse 0 end as JSTotal,\n" +
                "\t\t\t case when (ifnull(b.otco_Formula,'0')='0') or (b.otco_Formula='') then 0 else \n" +
                "\t\t\t\t cast(ifnull(b.otco_Formula,'0') as decimal(19,6)) end as UnderRate,\n" +
                "\t\t\t\tcase when (ifnull(b.otco_Formula,'0')='0') or (b.otco_Formula='') then 0 else \n" +
                "\t\t\t\t  cast(ifnull(b.otco_Formula,'0') as decimal(19,6)) end as HyperRate,\n" +
                "\t\t\t\tcase when (ifnull(a.otco_Formula,'0')='0') or (a.otco_Formula='') then 0 else \n" +
                "\t\t\t\t  cast(ifnull(a.otco_Formula,'0') as decimal(19,6)) end as Rate\n" +
                "\t\t\t  from td_OtherCost_Bid a\n" +
                "\t\t\t left join td_OtherCost b on a.otco_ProjectID=b.otco_ProjectID  \n" +
                "\t\t\t\tand a.otco_ManageNodeID=b.otco_ManageNodeID and a.otco_CostType=b.otco_CostType \n" +
                "\t\t\t\tand b.otco_ProjectID=:tdProjectId\n" +
                "\t\t\t left join td_ManageNode as c on a.otco_ProjectID=c.mano_ProjectID\n" +
                "\t\t\t\tand a.otco_ManageNodeID=c.mano_ManageNodeID\n" +
                "\t\t\t\tand c.mano_ProjectID=:tdProjectId\n" +
                "\t\t\t\t\t\t\t\t\t\tleft join td_ManageNode as d on c.mano_ProjectID=d.mano_ProjectID\n" +
                "\t\t\t\tand c.mano_ParentID=d.mano_ManageNodeID\n" +
                "\t\t\t\tand d.mano_ProjectID=:tdProjectId\n" +
                "\t\t\t  left join vi_budgetnodecost as e on a.otco_ProjectID=e.ProjectID \n" +
                "\t\t\t\tand a.enterprise_id=e.BidID and a.otco_ManageNodeID=e.ManageNodeID\n" +
                "\t\t\t   and e.ProjectID=:tdProjectId\n" +
                "\t\t\t left join vi_measuresnodecost as f on a.otco_ProjectID=f.ProjectID\n" +
                "\t\t\t\tand a.enterprise_id=f.BidID and a.otco_ManageNodeID=f.ManageNodeID\n" +
                "\t\t\t\tand f.ProjectID=:tdProjectId\n" +
                "\t\t\tleft join (select enterprise_id,cosu_ManageNodeID,sum(cosu_Total) as SJTotal\n" +
                "\t\t\t\t  from td_CostSum_Bid where cosu_ProjectID=:tdProjectId and cosu_CostType in ('1','2','3','4') \n" +
                "\t\t\t\t  group by enterprise_id,cosu_ManageNodeID) k \n" +
                "\t\t\t\ton a.enterprise_id=k.enterprise_id and a.otco_ManageNodeID=k.cosu_ManageNodeID\n" +
                "\t\t\t  where a.otco_ProjectID=:tdProjectId and c.mano_Type<>0\n" +
                "\t\t\t\tand (a.otco_CostType='4' or a.otco_CostType='5') \n" +
                "\t\t\t  union \n" +
                "\t\t\t  select a.otco_ProjectID as ProjectID,c.mano_ManageNodeID,a.otco_OtherCostID,\n" +
                "\t\t\t\tc.mano_Name as ManageNodeName,\n" +
                "\t\t\t\ta.otco_Name,a.otco_CostType as Type,'项' as Unit,a.enterprise_id as BidID,\n" +
                "\t\t\t\tifnull(a.otco_Total,0) as Total,\n" +
                "\t\t\t\tcase a.otco_CostType  \n" +
                "\t\t\t\t\t\t\t\t\t when '4' then ifnull(h.RGFTotal,0)+ifnull(h.JXFTotal,0)\n" +
                "\t\t\t\t\t\t\t\t\t when '5' then ifnull(d.ZTCSTotal,0)+ifnull(e.ZTQTTotal,0)+\n" +
                "\t\t\t\t\t\t\t\t\t\t\t\t\t ifnull(f.otco_Total,0)\n" +
                "\t\t\t   else 0 end as JSTotal,\n" +
                "\t\t\t\tcase when (ifnull(b.otco_Formula,'0')='0') or (b.otco_Formula='') then 0 else \n" +
                "\t\t\t\t  cast(ifnull(b.otco_Formula,'0') as decimal(19,6)) end as UnderRate,\n" +
                "\t\t\t\tcase when (ifnull(b.otco_Formula,'0')='0') or (b.otco_Formula='') then 0 else \n" +
                "\t\t\t\t  cast(ifnull(b.otco_Formula,'0') as decimal(19,6)) end as HyperRate,\n" +
                "\t\t\t\tcase when (ifnull(a.otco_Formula,'0')='0') or (a.otco_Formula='') then 0 else \n" +
                "\t\t\t\t  cast(ifnull(a.otco_Formula,'0') as decimal(19,6)) end as Rate\n" +
                "\t\t\t  from td_OtherCost_Bid a \n" +
                "\t\t\t  left join td_OtherCost b on a.otco_ProjectID=b.otco_ProjectID \n" +
                "\t\t\t\tand a.otco_ManageNodeID=b.otco_ManageNodeID and a.otco_CostType=b.otco_CostType \n" +
                "\t\t\t\tand b.otco_ProjectID=:tdProjectId\n" +
                "\t\t\t left join td_ManageNode as c on a.otco_ProjectID=c.mano_ProjectID\n" +
                "\t\t\t\tand a.otco_ManageNodeID=c.mano_ManageNodeID\n" +
                "\t\t\t\tand c.mano_ProjectID=:tdProjectId\n" +
                "\t\t\t  left join (select meno_ProjectID,enterprise_id,meno_ManageNodeID,sum(meno_Total) as ZTCSTotal \n" +
                "\t\t\t\t  from td_MeasuresNode_Bid where meno_ProjectID=:tdProjectId\n" +
                "\t\t\t\t  and meno_MeasuresNodeID='00000000-0000-0000-0000-000000000000'\n" +
                "\t\t\t\tgroup by meno_ProjectID,enterprise_id,meno_ManageNodeID) d \n" +
                "\t\t\t\ton a.otco_ManageNodeID=d.meno_ManageNodeID and a.otco_ProjectID=d.meno_ProjectID\n" +
                "\t\t\t   and a.enterprise_id=d.enterprise_id\n" +
                "\t\t\t left join (select otno_ProjectID,enterprise_id,otno_ManageNodeID,sum(otno_Total) as ZTQTTotal\n" +
                "\t\t\t\t  from td_OtherNode_Bid where otno_ProjectID=:tdProjectId\n" +
                "\t\t\t\t  and otno_OtherNodeID='00000000-0000-0000-0000-000000000000'\n" +
                "\t\t\t\t  group by otno_ProjectID,enterprise_id,otno_ManageNodeID) e \n" +
                "\t\t\t\ton a.otco_ManageNodeID=e.otno_ManageNodeID and a.otco_ProjectID=e.otno_ProjectID \n" +
                "\t\t\t   and a.enterprise_id=e.enterprise_id \n" +
                "\t\t\tleft join td_OtherCost_Bid f on a.otco_ProjectID=f.otco_ProjectID \n" +
                "\t\t\t   and a.otco_ManageNodeID=f.otco_ManageNodeID \n" +
                "\t\t\t   and a.enterprise_id=f.enterprise_id and f.otco_CostType='4' \n" +
                "\t\t\t\tand f.otco_ProjectID=:tdProjectId\n" +
                "\t\t\t  left join vi_measuresnodecost as h on a.otco_ProjectID=h.ProjectID \n" +
                "\t\t\t\tand a.enterprise_id=h.BidID and a.otco_ManageNodeID=h.ManageNodeID\n" +
                "\t\t\t\tand h.ProjectID=:tdProjectId\n" +
                "\t\t\t where a.otco_ProjectID=:tdProjectId and c.mano_Type=0\n" +
                "\t\t\t\tand (a.otco_CostType='4' or a.otco_CostType='5') \n" +
                "\t\t\t) X                                \n" +
                "\t\t   where (Type='4' and (UnderRate<>0 and round(Total,2)<round(JSTotal*UnderRate/100,2)*0.3-1)) \n" +
                "\t\t\t  or (Type='5' and ((UnderRate<>0 and round(Total,2)<=round(JSTotal*UnderRate/100,2)-1) or \n" +
                "\t\t\t\t\t\t\t\t   (HyperRate<>0 and round(Total,2)>=round(JSTotal*HyperRate/100,2)+1)))\n" +
                ") n on n.ProjectID = m.biin_ProjectID and n.BidID = m.enterprise_id\n" +
                "\t\t\twhere m.biin_ProjectID=:tdProjectId) a GROUP BY enterprise_id) c on b.seq_id = c.enterprise_id\n" +
                "\t\t\t\t\t\t where b.section_id =:sectionId", paramMap);
        //保存计算结果
        saveResult(beSectionStepJob.getSectionId(), beSectionStepJob.getSectionStepId(), enterList);
        return true;
    }

    /**
     * 保存计算结果
     * @param sectionId
     * @param sectionStepId
     * @param enterList
     * @throws Exception
     */
    private void saveResult(String sectionId, String sectionStepId, List<Map<String, Object>> enterList) throws Exception {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("sectionStepId", sectionStepId);
        paramMap.put("sectionId", sectionId);
        List<BeComputerResult> beComputerResultList = new ArrayList<>();
        BeComputerResult beComputerResult = null;
        for(Map<String, Object> map: enterList) {
            beComputerResult = new BeComputerResult();
            beComputerResult.setSeqId(IdsUtil.uuid());
            beComputerResult.setSectionId(sectionId);
            beComputerResult.setC1(OConvertUtils.getString(map.get("bfhs")));
            beComputerResult.setEnterpriseId(OConvertUtils.getString(map.get("enterpriseId")));
            beComputerResult.setSectionStepId(sectionStepId);
            beComputerResult.setCreateTime(DateUtil.now());
            //不符合数=0,代表通过
            if (OConvertUtils.getInt(map.get("bfhs")) == 0) {
                beComputerResult.setIsPassed("1");
            } else {
                beComputerResult.setIsPassed("0");
            }
            beComputerResultList.add(beComputerResult);
        }
        //删除已存在的结果，防止重复存入
        jdbcTemplateService.update(" DELETE FROM be_computer_result WHERE  section_step_id=:sectionStepId and section_id=:sectionId", paramMap);
        //批量转数组,插入数据
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(beComputerResultList);
        jdbcTemplateService.bathInsert("insert into be_computer_result (seq_id, section_step_id, section_id,enterprise_id, c1,create_time,is_passed)\n" +
                "    values(:seqId, :sectionStepId, :sectionId,:enterpriseId,:c1,:createTime,:isPassed)",beanSources);
    }
}
